from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, String, Integer, Float, BLOB, DATETIME, ForeignKey  # 引入字段定义类
from sqlalchemy.orm import sessionmaker, relationship
# 关联表
from sqlalchemy import Table


# 数据库地址
dbfile = "C:\\Users\\Administrator\\AppData\\Roaming\\DBeaverData\\workspace6\\.metadata\\sample-database-sqlite-1\\Chinook.db"
engine = create_engine('sqlite:///' + dbfile)
Base = declarative_base()


class Foo(Base):
    __tablename__ = 'foo'

    bar = Column(Integer, primary_key=True)
    baz = Column(String(20))


class Artist(Base):
    __tablename__ = 'Artist'

    ArtistId = Column(Integer, primary_key=True, nullable=False)
    Name = Column(String(120))


class Album(Base):

    __tablename__ = 'Album'

    AlbumId = Column(Integer, primary_key=True, nullable=False)
    Title = Column(String(160), nullable=False)
    ArtistId = Column(Integer, ForeignKey('Artist.ArtistId'), nullable=False)
    Artist = relationship("Artist", backref="Album")
    Column1 = Column(BLOB)


class Genre(Base):
    __tablename__ = 'Genre'

    GenreId = Column(Integer, primary_key=True, nullable=False)
    Name = (String(120))


class MediaType(Base):
    __tablename__ = 'MediaType'

    MediaTypeId = Column(Integer, primary_key=True, nullable=False)
    Name = (String(120))


class Track(Base):
    __tablename__ = 'Track'

    TrackId = Column(Integer, primary_key=True, nullable=False)
    Name = Column(String(200), nullable=False)
    Composer = Column(String(220))
    Milliseconds = Column(Integer, nullable=False)
    Bytes = Column(Integer)
    UnitPrice = Column(Float, nullable=False)
    # 外键
    AlbumId = Column(Integer, ForeignKey('Album.AlbumId'))
    Album = relationship("Album", backref="Track")
    GenreId = Column(Integer, ForeignKey('Genre.GenreId'))
    Genre = relationship("Genre", backref="Track")
    MediaTypeId = Column(Integer, ForeignKey('MediaType.MediaTypeId'), nullable=False)
    MediaType = relationship("MediaType", backref="Track")


playlist_track = Table(
    "PlaylistTrack",  # 表名
    Base.metadata,  # 表类型
    Column("PlaylistId", Integer,
           ForeignKey("Playlist.PlaylistId"), primary_key=True),
    Column("TrackId", Integer,
           ForeignKey("Track.TrackId"), primary_key=True))


class Playlist(Base):
    __tablename__ = 'Playlist'
    PlaylistId = Column(Integer, primary_key=True, nullable=False)
    Name = Column(String(120))
    Tracks = relationship("Track", backref="Playlist", secondary=playlist_track)


class Customer(Base):
    __tablename__ = 'Customer'
    CustomerId = Column(Integer, primary_key=True, nullable=False)
    FirstName = Column(String(40), nullable=False)
    LastName = Column(String(20), nullable=False)
    Company = Column(String(80))
    Address = Column(String(70))
    City = Column(String(40))
    State = Column(String(40))
    Country = Column(String(40))
    PostalCode = Column(String(10))
    Phone = Column(String(24))
    Fax = Column(String(24))
    Email = Column(String(60), nullable=False)
    # 外键
    SupportRepId = Column(Integer, ForeignKey('Employee.EmployeeId'))
    Employee = relationship("Employee", backref="Customer")


class Invoice(Base):
    __tablename__ = 'Invoice'
    InvoiceId = Column(Integer, primary_key=True, nullable=False)
    InvoiceDate = Column(DATETIME, nullable=False)
    BillingAddress = Column(String(70))
    BillingCity = Column(String(40))
    BillingState = Column(String(40))
    BillingCountry = Column(String(40))
    BillingPostalCode = Column(String(10))
    Total = Column(Float, nullable=False)
    # 外键
    CustomerId = Column(Integer, ForeignKey("Customer.CustomerId"))
    Customer = relationship("Customer", backref="Invoice")


class InvoiceLine(Base):
    __tablename__ = 'InvoiceLine'
    InvoiceLineId = Column(Integer, primary_key=True, nullable=False)
    UnitPrice = Column(Float, nullable=False)
    Quantity = Column(Integer, nullable=False)
    # 外键
    InvoiceId = Column(Integer, ForeignKey("Invoice.InvoiceId"), nullable=False)
    Invoice = relationship("Invoice", backref="InvoiceLine")
    TrackId = Column(Integer, ForeignKey("Track.TrackId"), nullable=False)
    Track = relationship("Track", backref="InvoiceLine")


# 查询Accept的Artist所有Album的所有Track的所有购买信息
if __name__ == '__main__':
    Session = sessionmaker(bind=engine)
    session = Session()
    artist_list = session.query(Artist).filter_by(Name='Accept').first()
    invoiceline_list = session.query(InvoiceLine).all()
    playlist = session.query(Playlist).first()
    print('艺术家{}的专辑和单曲购买信息如下：'.format(artist_list.Name))
    for track in playlist.Tracks:
        if track.Album.Artist.Name == artist_list.Name:
            for invoiceline in invoiceline_list:
                if invoiceline.TrackId == track.TrackId:
                    print('专辑：{}\t单曲：{}\t单价：{}元\t数量：{}个\t顾客：{}▪{}\t购买日期：{}'
                          .format(track.Album.Title, track.Name, track.UnitPrice, invoiceline.Quantity, invoiceline.Invoice.Customer.FirstName, invoiceline.Invoice.Customer.LastName, invoiceline.Invoice.InvoiceDate))
    session.close()



